<!doctype html>
<html>

	<head>
		<meta charset="UTF-8">
		<title></title>
		<meta name="viewport" content="width=device-width,initial-scale=1,minimum-scale=1,maximum-scale=1,user-scalable=no" />
		<link href="../css/mui.min.css" rel="stylesheet" />
		<script type="text/javascript" src="../js/other/jquery-3.2.1.js"></script>
		<script type="text/javascript" src="../js/other/IOUtil.js"></script>
		<script src="../js/mui.min.js"></script>
		<script type="text/javascript">
			mui.init();
			// 扩展API加载完毕后调用onPlusReady回调函数 
			document.addEventListener("plusready", onPlusReady, false);
			//扩展API加载完毕后调用onPlusReady回调函数 
			function onPlusReady() {
				//getDBBuf();
			}

			//初始化
			var worker = new Worker("../js/other/worker.sql.js");
			worker.onerror = error;

			/**
			 * 打开数据库(内存创建，不从db文件读取，也不保存为db文件)
			 */
			function emptyOpenDB() {
				worker.postMessage({
					action: 'open'
				});
			};

			/**
			 * 从已有数据库文件打开数据库 
			 * @param {Object} ArrayBuffer (数据库文件的字节数组)
			 */
			function openDBFromData(ArrayBuffer) {
				worker.postMessage({
					id: 1,
					action: 'open',
					buffer: ArrayBuffer,
					/*Optional. An ArrayBuffer representing an SQLite Database file*/
				});
			};

			/**
			 * 执行失败的回调
			 * @param {Object} e
			 */
			function error(e) {
				console.log("worker.sql.js 失败 :" + e.message);
				alert("worker.sql.js 失败 :" + e.message);
			}

			/**
			 *执行sqlite语句 
			 */
			function execSqls(commands) {
				var c = commands + ';';
				worker.onmessage = function(event) {
					var results = event.data.results; // The result of the query
					display(results);
				}
				worker.postMessage({
					action: 'exec',
					sql: c
				});

			};

			/**
			 * 从本地文件读取数据库的字节数组
			 */
			function getDBBuf() {
				plus.io.resolveLocalFileSystemURL("_downloads/app.db.text", function(entry) {
					entry.file(function(file) {
						var fileReader = new plus.io.FileReader();
						fileReader.onloadend = function(evt) {
							console.log("本地数据库读取 ： " + "evt.target" + evt.target);
							//alert("本地数据库读取 ： " + evt.target.result);
							var diskDBArrBuf = Str2Bytes(evt.target.result);
							openDBFromData(diskDBArrBuf);
						}
						fileReader.readAsText(file, 'utf-8');
						console.log("本地数据库读取 ： " + file.size + '--' + file.name);
					});
				}, function(e) {
					alert("Resolve file URL failed: " + e.message);
				});
			};

			/**
			 *导出数据库文件持久化 
			 */
			//数据库文件的字节数组
			var arraybuff;

			function exportDBfile() {
				worker.onmessage = function(event) {
					arraybuff = event.data.buffer;
					//var blob = new Blob([arraybuff]);

					plus.io.requestFileSystem(plus.io.PUBLIC_DOWNLOADS, function(fs) {
						// 可通过fs进行文件操作 
						console.log("File system name: " + fs.name);
						console.log("File system root: " + fs.root.fullPath);
						// 通过fs.root获取DirectoryEntry对象进行操作 
						console.log("数据库文件字节数组长度:" + arraybuff.length);
						//创建文件写文件
						fs.root.getFile('app.db.text', {
							create: true,
						}, function(fileEntry) {
							fileEntry.createWriter(function(writer) {

								writer.onwrite = function(e) {
									plus.console.log("Write data success!");
								};
								writer.onerror = function(e) {
									alert("写数据失败:" + e.target);
								};
								// Write data to the end of file.
								alert("writer.length = " + writer.length);
								writer.seek(0);
								writer.write(Bytes2Str(arraybuff));
								//writer.write("彭子良是么？");
							}, function(e) {
								alert("createWriter失败:" + e.message);
							});
						}, function(e) {
							alert("创建文件失败: " + e.message);
						});

					}, function(e) {
						alert("请求文件系统失败: " + e.message);
					});

				};

				//db导出命令请求
				worker.postMessage({
					action: 'export'
				});

			};

			/**
			 * 展示数据库数据
			 */
			function display(results) {
				$("#showDB").empty();
				var showDBDiv = document.getElementById('showDB');
				for(var i = 0; i < results.length; i++) {
					showDBDiv.appendChild(tableCreate(results[i].columns, results[i].values));
				}
			};

			/**
			 * 创建显示DB的表格
			 */
			var tableCreate = function() {
				function valconcat(vals, tagName) {
					if(vals.length === 0) return '';
					var open = '<' + tagName + '>',
						close = '</' + tagName + '>';
					return open + vals.join(close + open) + close;
				}
				return function(columns, values) {
					var tbl = document.createElement('table');
					var html = '<thead>' + valconcat(columns, 'th') + '</thead>';
					var rows = values.map(function(v) {
						return valconcat(v, 'td');
					});
					html += '<tbody>' + valconcat(rows, 'tr') + '</tbody>';
					tbl.innerHTML = html;
					return tbl;
				}
			}();
		</script>
	</head>

	<body>

		<div class="mui-content" align="center">
			<div class="mui-card" style="padding: 8px;">
				It uses a virtual database file stored in memory, and thus does’nt persist the changes made to the database. However, it allows you to import any existing sqlite file, and to export the created database as a javascript typed array.
				<br /> 数据是在内存中操作，可以导入db文件，可以把数据导出持久化到db文件
				<br />
				<a href="http://kripken.github.io/sql.js/GUI/">http://kripken.github.io/sql.js/GUI/</a>
			</div>

			<div align="center" style="padding: 8px;">

				<textarea id="sql" style="font-size: 12px;height:300px">DROP TABLE IF EXISTS employees;
CREATE TABLE employees( id          integer,  name    text,designation text,     manager integer,hired_on    date,     salary  integer,commission  float,    dept    integer);

  INSERT INTO employees VALUES (1,'JOHNSON','ADMIN',6,'1990-12-17',18000,NULL,4);
  INSERT INTO employees VALUES (2,'HARDING','MANAGER',9,'1998-02-02',52000,300,3);
  INSERT INTO employees VALUES (3,'TAFT','SALES I',2,'1996-01-02',25000,500,3);
  INSERT INTO employees VALUES (4,'HOOVER','SALES I',2,'1990-04-02',27000,NULL,3);
  INSERT INTO employees VALUES (5,'LINCOLN','TECH',6,'1994-06-23',22500,1400,4);
  INSERT INTO employees VALUES (6,'GARFIELD','MANAGER',9,'1993-05-01',54000,NULL,4);
  INSERT INTO employees VALUES (7,'POLK','TECH',6,'1997-09-22',25000,NULL,4);
  INSERT INTO employees VALUES (8,'GRANT','ENGINEER',10,'1997-03-30',32000,NULL,2);
  INSERT INTO employees VALUES (9,'JACKSON','CEO',NULL,'1990-01-01',75000,NULL,4);
  INSERT INTO employees VALUES (10,'FILLMORE','MANAGER',9,'1994-08-09',56000,NULL,2);
  INSERT INTO employees VALUES (11,'ADAMS','ENGINEER',10,'1996-03-15',34000,NULL,2);
  INSERT INTO employees VALUES (12,'WASHINGTON','ADMIN',6,'1998-04-16',18000,NULL,4);
  INSERT INTO employees VALUES (13,'MONROE','ENGINEER',10,'2000-12-03',30000,NULL,2);
  INSERT INTO employees VALUES (14,'ROOSEVELT','CPA',9,'1995-10-12',35000,NULL,1);

SELECT designation,COUNT(*) AS nbr, (AVG(salary)) AS avg_salary FROM employees GROUP BY designation ORDER BY avg_salary DESC;
SELECT name,hired_on FROM employees ORDER BY hired_on;</textarea>

				<span style="color: red;">结果展示</span>
				<div id="showDB" align="center" style="margin-bottom: 5px;">
				</div>

				<textarea id="searchSql" hidden="hidden">
					SELECT * FROM employees ;
				</textarea>

				<button type="button" class="mui-btn" onclick="execSqls($('#sql').text())">执行</button>
				<button type="button" class="mui-btn" onclick="execSqls($('#searchSql').text())">查看表数据</button>
				<button type="button" class="mui-btn" onclick="exportDBfile()">主动持久化</button>
				<button type="button" class="mui-btn" style="display: block;" onclick="execSqls('delete from employees where id = 14')">del id=14</button>
			</div>

		</div>

	</body>

</html>